﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using BEELab.Data;
using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace BEELab.Logic.ProgramManagement.StageManagement
{
    public static class DataElements
    {
        public class DataElement
        {
            public DataElement(int ID, DataTable data)
            {
                DataRow[] rows = data.Select("ID=" + ID);

                if (rows.Length == 1)
                {
                    DataRow dr = rows[0];
                    this.id = ID;
                    this.label = dr["Label"].ToString();
                    this.variableID = int.Parse(dr["VariableID"].ToString());
                    this.stageID = int.Parse(dr["StageID"].ToString());
                }
            }

            public DataElement(int ID)
            {
                SqlCommand command = new SqlCommand();
                command.CommandText = "SELECT * FROM DataElements WHERE ID=@ID";
                command.Parameters.Add(new SqlParameter("@ID", ID));
                DataTable dt = DBHelper.ExecuteQuery(command);

                if (dt.Rows.Count == 1)
                {
                    DataRow dr = dt.Rows[0];
                    this.id = ID;
                    this.label = dr["Label"].ToString();
                    this.variableID = int.Parse(dr["VariableID"].ToString());
                    this.stageID = int.Parse(dr["StageID"].ToString());
                }
            }

            private int stageID;
            public int StageID
            {
                get { return stageID; }
            }

            private int id;
            public int ID
            {
                get { return id; }
            }

            private string label;
            public string Label
            {
                get { return label; }
            }

            private int variableID;
            public int VariableID
            {
                get { return variableID; }
            }
        }

        public static int ImportElement(int ElementID, int StageID, string NamePrefix)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "SELECT * FROM DataElements WHERE ID=@ID";
            command.Parameters.Add(new SqlParameter("@ID", ElementID));
            DataTable dt = DBHelper.ExecuteQuery(command);

            if (dt.Rows.Count != 1)
                return -1;

            DataRow dr = dt.Rows[0];
            
            int varID = VariableManagement.VariableManagement.GetIDByName(StageManagement.GetProgramID(StageID), VariableManagement.VariableManagement.GetNameByID(int.Parse(dr["VariableID"].ToString())));

            int ID = CreateElement(StageID, NamePrefix + dr["Label"].ToString(), varID);

            return ID;
        }

        /// <summary>
        /// Insert new DataElement into DB
        /// </summary>
        /// <param name="StageID">Stage ID</param>
        /// <param name="Label">Label</param>
        /// <param name="VariableID">Variable ID</param>
        /// <returns>Number of rows effected on DB</returns>
        public static int CreateElement(int StageID, string Label, int VariableID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "INSERT INTO DataElements (StageID, Label, VariableID) VALUES (@StageID, @Label, @VariableID); SELECT SCOPE_IDENTITY();";
            command.Parameters.Add(new SqlParameter("@StageID", StageID));
            command.Parameters.Add(new SqlParameter("@Label", Label));
            command.Parameters.Add(new SqlParameter("@VariableID", VariableID));

            return int.Parse(DBHelper.ExecuteScalar(command));
        }

        public static Boolean DeleteElement(int ID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "DELETE FROM DataElements WHERE ID=@ID";
            command.Parameters.Add(new SqlParameter("@ID", ID));

            int res = DBHelper.ExecuteNonQuery(command);

            if (res > 0)
                return true;

            return false;
        }

        public static int GetIDByLabel(int StageID, string Label)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "SELECT ID FROM DataElements WHERE StageID=@StageID AND Label=@Label";
            command.Parameters.Add(new SqlParameter("@StageID", StageID));
            command.Parameters.Add(new SqlParameter("@Label", Label));
            return int.Parse(DBHelper.ExecuteScalar(command));
        }

        public static string GetLabelByID(int ID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "SELECT Label FROM DataElements WHERE ID=@ID";
            command.Parameters.Add(new SqlParameter("@ID", ID));
            return DBHelper.ExecuteScalar(command);
        }

        public static Boolean DeleteElement(int StageID, string Label)
        {
            return DeleteElement(GetIDByLabel(StageID,Label));
        }

        public static Boolean UpdateElement(int ID, string Label, int VariableID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "UPDATE DataElements SET Label=@Label, VariableID=@VariableID WHERE ID=@ID";
            command.Parameters.Add(new SqlParameter("@Label", Label));
            command.Parameters.Add(new SqlParameter("@VariableID", VariableID));
            command.Parameters.Add(new SqlParameter("@ID", ID));

            int res = DBHelper.ExecuteNonQuery(command);

            if (res == 1)
                return true;

            return false;
        }

        public static List<int> ListByStageID(int ID)
        {
            List<int> result = new List<int>();

            SqlCommand command = new SqlCommand();
            command.CommandText = "SELECT ID FROM DataElements WHERE StageID=@StageID";
            command.Parameters.Add(new SqlParameter("@StageID", ID));
            DataTable dt = DBHelper.ExecuteQuery(command);

            foreach (DataRow dr in dt.Rows)
            {
                result.Add((int)dr["ID"]);
            }

            return result;
        }

        /// <summary>
        /// Gets Stage ID and returns string that contains all of this stage Date elements names,
        /// seperate by seperator.
        /// </summary>
        /// <param name="StageID">Stage ID</param>
        /// <param name="separator">separator</param>
        /// <returns>String that contains all of this stage Data Elements names</returns>
        public static string EnumerateDataElementsForStage(int StageID, string separator)
        {
            StringBuilder res = new StringBuilder();

            SqlCommand command = new SqlCommand();
            command.CommandText = "Select Label FROM DataElements WHERE StageID=@StageID";
            command.Parameters.Add(new SqlParameter("@StageID", StageID));
            DataTable dt = DBHelper.ExecuteQuery(command);

            bool isFirst = true;

            foreach (DataRow row in dt.Rows)
            {
                if (!isFirst)
                {
                    res.Append(separator);
                }
                else
                {
                    isFirst = false;
                }
                res.Append(row[0].ToString());
                res.Append("$");
            }

            return res.ToString();
        }

        public static string EnumerateDataElementsForEdit(int StageID, string Name, string separator)
        {
            StringBuilder res = new StringBuilder();

            SqlCommand command = new SqlCommand();
            command.CommandText = "Select Label FROM DataElements WHERE StageID=@StageID";
            command.Parameters.Add(new SqlParameter("@StageID", StageID));
            DataTable dt = DBHelper.ExecuteQuery(command);

            bool isFirst = true;

            foreach (DataRow row in dt.Rows)
            {
                if (row[0].ToString() != Name)
                {
                    if (!isFirst)
                    {
                        res.Append(separator);
                    }
                    else
                    {
                        isFirst = false;
                    }
                    res.Append(row[0].ToString());
                    res.Append("$");
                }
            }

            return res.ToString();
        }

        public static int GetOwnerID(int ID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "SELECT Programs.OwnerID FROM DataElements JOIN Stages ON DataElements.StageID=Stages.ID JOIN Programs ON Programs.ID=Stages.ProgramID WHERE DataElements.ID=@ID";
            command.Parameters.Add(new SqlParameter("@ID", ID));
            string res = DBHelper.ExecuteScalar(command);

            if (res != null && res.Length > 0)
            {
                return int.Parse(res);
            }

            return 0;
        }
    }
}